package cn.net.qinling.xmlaccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public abstract class JDBCDataAccess implements DataAccess {
	
	protected final String INSERT_SQL = "insert into documents values (?, ?)";

	protected final String QUERY_RECORD_UID = "select * from documents where id=\"%s\"";

	protected final String QUERY_RECORD_PATIENTID = "select id from documents where XMLEXISTS('declare default element namespace \"urn:hl7-org:v3\"; $d/ClinicalDocument/recordTarget/patientRole/id[@root=\"SD.4.2\" and @extension=\"%s\"]' passing content as \"d\")";

	protected final String QUERY_PATIENT_CITY_SEX = "select id from documents where XMLEXISTS ('declare default element namespace \"urn:hl7-org:v3\"; $d/ClinicalDocument/recordTarget/patientRole[addr/city=\"%s\" and patient/administrativeGenderCode/@code=\"%s\"]' passing content as \"d\")";
	
	protected final String QUERY_RECORD_AUTHOR_ADDR = "select id from documents where XMLEXISTS ('declare default element namespace \"urn:hl7-org:v3\"; $d/ClinicalDocument/author/assignedAuthor/representedOrganization/name[contains(text(), \"%s\")]' passing content as \"d\")";

	
	protected Connection conn = null;

	@Override
	public List<String> listRecordsByPatientId(String pid) {
		try {
			String sql = String.format(QUERY_RECORD_PATIENTID, pid);

			return this.listResult(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return null;
	}

	@Override
	public List<String> listRecordsByPatientDemographyInfo(String gender,
			String city) {
		try {
			String sql = String.format(QUERY_PATIENT_CITY_SEX, city, gender);
			return this.listResult(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return null;
	}

	@Override
	public List<String> listRecordsByPatientPartialAddress(String partialAddress) {
		try {
			String sql = String
					.format(QUERY_RECORD_AUTHOR_ADDR, partialAddress);
			return this.listResult(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return null;
	}
	
	protected List<String> listResult(String sql) throws SQLException {
		System.out.println(sql);
		List<String> result = new LinkedList<String>();
		PreparedStatement stmt = null;
		try {
			stmt = this.conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				result.add(rs.getString(1));
			}
		} finally {
			stmt.close();
		}
		
		return result;
	}

}
